package cn.fldong.dao.impl;

import cn.fldong.dao.ScoreDAO;
import cn.fldong.model.Score;
import cn.fldong.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.math.BigDecimal;

/**
 * ScoreDAO接口的实现类，提供成绩相关的数据库操作实现
 */
public class ScoreDAOImpl implements ScoreDAO {
    private static final String GET_SCORE_BY_ID = "SELECT * FROM scores WHERE score_id = ?";
    private static final String GET_SCORES_BY_STUDENT_ID = "SELECT * FROM scores WHERE student_id = ? ORDER BY course_id";
    private static final String GET_SCORES_BY_COURSE_ID = "SELECT * FROM scores WHERE course_id = ? ORDER BY student_id";
    private static final String GET_SCORE_BY_STUDENT_AND_COURSE = "SELECT * FROM scores WHERE student_id = ? AND course_id = ?";
    private static final String GET_ALL_SCORES = "SELECT * FROM scores ORDER BY score_id";
    private static final String ADD_SCORE = "INSERT INTO scores(score_id, student_id, course_id, score_value, exam_date, weight, grade, comments) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_SCORE = "UPDATE scores SET score_value=?, exam_date=?, weight=?, grade=?, comments=? WHERE score_id=?";
    private static final String DELETE_SCORE = "DELETE FROM scores WHERE score_id = ?";

    /**
     * 将ResultSet映射为Score对象的私有辅助方法
     */
    private Score mapResultSetToScore(ResultSet rs) throws SQLException {
        Score score = new Score();
        score.setScoreId(rs.getInt("score_id"));
        score.setStudentId(rs.getString("student_id"));
        score.setCourseId(rs.getString("course_id"));
        score.setScore(rs.getBigDecimal("score_value"));
        score.setExamDate(rs.getDate("exam_date"));
        score.setWeight(rs.getInt("weight"));
        score.setGrade(rs.getString("grade"));
        score.setSemester(rs.getString("comments"));
        score.setCreatedAt(rs.getTimestamp("created_at"));
        score.setUpdatedAt(rs.getTimestamp("updated_at"));
        return score;
    }

    @Override
    public Score getScoreById(String scoreId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Score score = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_SCORE_BY_ID);
            stmt.setString(1, scoreId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                score = mapResultSetToScore(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return score;
    }
    
    @Override
    public boolean scoreExists(String studentId, String courseId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM scores WHERE student_id = ? AND course_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, studentId);
            stmt.setString(2, courseId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }
    }

    @Override
    public List<Score> getScoresByStudentId(String studentId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Score> scoreList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_SCORES_BY_STUDENT_ID);
            stmt.setString(1, studentId);
            rs = stmt.executeQuery();

            while (rs.next()) {
                scoreList.add(mapResultSetToScore(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return scoreList;
    }

    @Override
    public List<Score> getScoresByCourseId(String courseId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Score> scoreList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_SCORES_BY_COURSE_ID);
            stmt.setString(1, courseId);
            rs = stmt.executeQuery();

            while (rs.next()) {
                scoreList.add(mapResultSetToScore(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return scoreList;
    }

    @Override
    public Score getScoreByStudentAndCourse(String studentId, String courseId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Score score = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_SCORE_BY_STUDENT_AND_COURSE);
            stmt.setString(1, studentId);
            stmt.setString(2, courseId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                score = mapResultSetToScore(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return score;
    }

    @Override
    public List<Score> getAllScores() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Score> scoreList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_ALL_SCORES);
            rs = stmt.executeQuery();

            while (rs.next()) {
                scoreList.add(mapResultSetToScore(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return scoreList;
    }

    @Override
    public boolean addScore(Score score) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(ADD_SCORE);
            stmt.setInt(1, score.getScoreId());
            stmt.setString(2, score.getStudentId());
            stmt.setString(3, score.getCourseId());
            stmt.setBigDecimal(4, score.getScore());
            stmt.setDate(5, new java.sql.Date(score.getExamDate().getTime()));
            stmt.setInt(6, score.getWeight());
            stmt.setString(7, score.getGrade());
            stmt.setString(8, score.getSemester());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean updateScore(Score score) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(UPDATE_SCORE);
            stmt.setBigDecimal(1, score.getScore());
            stmt.setDate(2, new java.sql.Date(score.getExamDate().getTime()));
            stmt.setInt(3, score.getWeight());
            stmt.setString(4, score.getGrade());
            stmt.setString(5, score.getSemester());
            stmt.setInt(6, score.getScoreId());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean deleteScore(String scoreId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(DELETE_SCORE);
            stmt.setString(1, scoreId);
            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }
}